package org.proddevplm.dao.core;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.proddevplm.dao.api.IDataAccessMgmt;
import org.proddevplm.dao.data.Resource;
import org.proddevplm.dao.data.changeReport.ChangeReport;
import org.proddevplm.dao.data.competitors.Competitors;
import org.proddevplm.dao.data.competitors.CompetitorsProduct;
import org.proddevplm.dao.data.conceptSelection.PughAnalysis;
import org.proddevplm.dao.data.correlations.QFD;
import org.proddevplm.dao.data.designForX.DesignForX;
import org.proddevplm.dao.data.designForX.DfXGuidelines;
import org.proddevplm.dao.data.designForX.DfXImportances;
import org.proddevplm.dao.data.designForX.DfXRelMatrix;
import org.proddevplm.dao.data.designInputs.CTQs;
import org.proddevplm.dao.data.designInputs.DesignElements;
import org.proddevplm.dao.data.designInputs.ProductFunctions;
import org.proddevplm.dao.data.prioritize.AHPAnalysis;
import org.proddevplm.dao.data.productFollowUp.productionPhase.ManufacturingItinerary;
import org.proddevplm.dao.data.productFollowUp.productionPhase.Process;
import org.proddevplm.dao.data.productFollowUp.usePhase.Complaints;
import org.proddevplm.dao.data.productFollowUp.usePhase.Suggestions;
import org.proddevplm.dao.data.requirements.Requirements;
import org.proddevplm.dao.data.requirements.VOCTable;
import org.proddevplm.dao.data.stakeholders.Stakeholder;
import org.proddevplm.dao.data.swot.SwotAnalysis;
import org.proddevplm.dao.util.HibernateUtil;

public class DataAccessMgmt implements IDataAccessMgmt{
	
	@Override
	public List<CompetitorsProduct> getCompetitorsProductByProductId(
			int productId) {
		
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select cp from CompetitorsProduct cp where cp.productId=:productId");
		q.setInteger("productId", productId);	
		return q.list();
	}

	@Override
	public List<Competitors> getCompetitorByProductId(int productId) {
		
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select c from Competitors c where c.productId=:productId");
		q.setInteger("productId", productId);
		return q.list();
	}

	@Override
	public String getCompetitorProductsByComp(String competitor, int productId) {
		
		String result="";
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select c from CompetitorsProduct c where c.competitor=:competitor and c.productId=:productId");
		q.setString("competitor", competitor);
		q.setInteger("productId", productId);
		List<CompetitorsProduct> listResults = q.list();
		for(CompetitorsProduct c:listResults){
			result += c.getCompetitorProduct()+"~";
		}		
		return result;
	}

	@Override
	public String getTechCharByProduct(String product, int productId) {

		String result="";
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select c from CompetitorsProduct c where c.competitorProduct=:product and c.productId=:productId");
		q.setString("product", product);
		q.setInteger("productId", productId);
		List<CompetitorsProduct> listResults = q.list();
		for(CompetitorsProduct c:listResults){
			result+=c.getTechnicalCharacteristics();
		}
		return result;
	}

	@Override
	public boolean getStakeholdersByNameandProdId(String name, int productId) {
		
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select sh from Stakeholder sh where sh.shName=:name and sh.productId=:productId");
		q.setString("name", name);
		q.setInteger("productId", productId);
		List<Stakeholder> shResults = q.list();
		
		if(shResults.size()==0) return false;
		else return true;
	}

	@Override
	public List<Stakeholder> getStakeholdersByProductId(int productId) {
		
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select sh from Stakeholder sh where sh.productId=:productId");
		q.setInteger("productId", productId);

		return q.list();
	}

	@Override
	public List<Requirements> getRequirementsByProductId(int productId) {
		
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("Select r from Requirements r where r.productId=:productId");
		q.setInteger("productId", productId);
				
		return q.list();
	}

	@Override
	public Requirements getRequirementByName(String requirement) {
		
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select r from Requirements r where r.requirement=:requirement");
		q.setString("requirement", requirement);
		return (Requirements)q.uniqueResult();
	}

	@Override
	public Requirements getRequirementById(int requirementId) {

		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select r from Requirements r where r.id=:requirementId");
		q.setInteger("requirementId", requirementId);
		return (Requirements)q.uniqueResult();
	}

	@Override
	public List<VOCTable> getVOCByProductId(int productId) {
		
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select v from VOCTable v where v.productId=:productId");
		q.setInteger("productId", productId);
		return q.list();
	}

	@Override
	public List<ProductFunctions> getFunctionsByProductId(int productId) {
		
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select f from ProductFunctions f where f.productId=:productId");
		q.setInteger("productId", productId);
		return q.list();
	}

	@Override
	public List<CTQs> getCTQsByProductId(int productId) {

		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select c from CTQs c where c.productId=:productId");
		q.setInteger("productId", productId);
		return q.list();
	}

	@Override
	public List<DesignElements> getDEByProductId(int productId) {
		
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select de from DesignElements de where de.productId=:productId");
		q.setInteger("productId", productId);
		return q.list();
	}

	@Override
	public List<CompetitorsProduct> getCPByActNameandProdId(String name,
			int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select cp from CompetitorsProduct cp where cp.activityName=:name and cp.productId=:productId");
		q.setString("name", name);
		q.setInteger("productId", productId);
		return q.list();
	}

	@Override
	public List<SwotAnalysis> getSwotByActNameandProdId(String activityName,
			int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select s from SwotAnalysis s where s.activityName=:activityName and s.productId=:productId");
		q.setString("activityName", activityName);
		q.setInteger("productId", productId);
		return q.list();
	}

	@Override
	public SwotAnalysis getSwotByNameandProdId(String name, int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select s from SwotAnalysis s where s.name=:name and s.productId=:productId");
		q.setString("name", name);
		q.setInteger("productId", productId);
		return (SwotAnalysis) q.uniqueResult();
	}

	@Override
	public List<Stakeholder> getSHByActNameandProdId(String activityName,
			int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select s from Stakeholder s where s.activityName=:activityName and s.productId=:productId");
		q.setString("activityName", activityName);
		q.setInteger("productId", productId);
		
		return q.list();
	}

	@Override
	public List<Requirements> getReqByActNameandProdId(String activityName,
			int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select r from Requirements r where r.activityName=:activityName and r.productId=:productId");
		q.setString("activityName", activityName);
		q.setInteger("productId", productId);
		return q.list();
	}

	@Override
	public List<VOCTable> getVOCByActNameandProdId(String activityName,
			int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select v from VOCTable v where v.activityName=:activityName and v.productId=:productId");
		q.setString("activityName", activityName);
		q.setInteger("productId", productId);
		return q.list();
	}

	@Override
	public List<AHPAnalysis> getAHPByActNameandProdId(String activityName,
			int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select a from AHPAnalysis a where a.activityName=:activityName and a.productId=:productId");
		q.setString("activityName", activityName);
		q.setInteger("productId", productId);
		return q.list();
	}

	@Override
	public List<ProductFunctions> getFunctionsByActNameandProdId(
			String activityName, int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select pf from ProductFunctions pf where pf.activityName=:activityName and pf.productId=:productId");
		q.setString("activityName", activityName);
		q.setInteger("productId", productId);
		return q.list();
	}

	@Override
	public List<CTQs> getCTQByActNameandProdId(String activityName,
			int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select c from CTQs c where c.activityName=:activityName and c.productId=:productId");
		q.setString("activityName", activityName);
		q.setInteger("productId", productId);
		return q.list();
	}

	@Override
	public List<DesignElements> getDEByActNameandProdId(String activityName,
			int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select d from DesignElements d where d.activityName=:activityName and d.productId=:productId");
		q.setString("activityName", activityName);
		q.setInteger("productId", productId);
		return q.list();
	}

	@Override
	public List<ChangeReport> getCRByActNameandProdId(String activityName,
			int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select cr from ChangeReport cr where cr.activityName=:activityName and cr.productId=:productId");
		q.setString("activityName", activityName);
		q.setInteger("productId", productId);
		
		return q.list();
	}

	@Override
	public ChangeReport getCRBySubjectandProdId(int id, int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select cr from ChangeReport cr where cr.id=:id and cr.productId=:productId");
		q.setInteger("id", id);
		q.setInteger("productId", productId);
		return (ChangeReport)q.uniqueResult();
	}

	@Override
	public List<ManufacturingItinerary> getMIByProductId(int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select mi from ManufacturingItinerary mi where mi.productId=:productId");
		q.setInteger("productId", productId);
		return q.list();
	}

	@Override
	public List<Process> getProcessByProductId(int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select p from Process p where p.productId=:productId");
		q.setInteger("productId", productId);
		return q.list();
	}

	@Override
	public List<Suggestions> getSuggestionsByProdId(int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select s from Suggestions s where s.productId=:productId");
		q.setInteger("productId", productId);
		return q.list();
	}

	@Override
	public List<Complaints> getComplaintsByProdId(int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select c from Complaints c where c.productId=:productId");
		q.setInteger("productId", productId);
		return q.list();
	}

	@Override
	public PughAnalysis getPughByActNameandProdId(String activityName, int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select pa from PughAnalysis pa where pa.activityName=:activityName and pa.productId=:productId");
		q.setString("activityName", activityName);
		q.setInteger("productId", productId);
		return (PughAnalysis)q.uniqueResult();
	}

	@Override
	public Stakeholder getSHByNameandProdId(String shName, int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select s from Stakeholder s where s.shName=:shName and s.productId=:productId");
		q.setString("shName", shName);
		q.setInteger("productId", productId);
		return (Stakeholder)q.uniqueResult();
	}

	@Override
	public Requirements getReqByNameandProdId(String requirement, int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select req from Requirements req where req.requirement=:requirement and req.productId=:productId");
		q.setString("requirement", requirement);
		q.setInteger("productId", productId);
		return (Requirements)q.uniqueResult();
	}

	@Override
	public QFD getQFDByActNameandProdId(String activityName, int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select q from QFD q where q.activityName=:activityName and q.productId=:productId");
		q.setString("activityName", activityName);
		q.setInteger("productId", productId);
		return (QFD)q.uniqueResult();
	}

	@Override
	public List<DesignForX> getAllDfX() {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select dfx from DesignForX as dfx");
		return q.list();
	}

	@Override
	public DesignForX getDfXById(int dfxId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select dfx from DesignForX dfx where dfx.id=:dfxId");
		q.setInteger("dfxId", dfxId);
		return (DesignForX)q.uniqueResult();
	}

	@Override
	public List<DfXGuidelines> getGuidelinesByDfxId(int dfxId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select g from DfXGuidelines g where g.dfx.id=:dfxId");
		q.setInteger("dfxId", dfxId);
		return q.list();
	}

	@Override
	public DfXGuidelines getSpecificGuidelineById(int dfxGId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select g from DfXGuidelines g where g.id=:dfxGId");
		q.setInteger("dfxGId", dfxGId);
		return (DfXGuidelines)q.uniqueResult();
	}

	@Override
	public List<DesignForX> getDfXByIds(String[] ids) {
		Session session = HibernateUtil.currentSession();
		List<Integer> dfxIds = new ArrayList<Integer>();
		for (String str: ids) {
			   dfxIds.add(Integer.valueOf(str));
			}
		Query q = session.createQuery("select d from DesignForX d where d.id in (:dfxIds)");
		q.setParameterList("dfxIds", dfxIds); 
		return q.list();
	}

	@Override
	public List<DfXGuidelines> getGuidelinesByDfxIds(String[] ids) {
		Session session = HibernateUtil.currentSession();
		List<Integer> dfxIds = new ArrayList<Integer>();
		for (String str: ids) {
			   dfxIds.add(Integer.valueOf(str));
			}
		Query q = session.createQuery("select g from DfXGuidelines g where g.dfx.id in (:dfxIds)");
		q.setParameterList("dfxIds", dfxIds); 
		return q.list();
	}

	@Override
	public List<DfXRelMatrix> dfxRelMatrixByActNameandProdId(
			String activityName, int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select drm from DfXRelMatrix drm where drm.activityName=:activityName and drm.product.id=:productId");
		q.setString("activityName", activityName);
		q.setInteger("productId", productId);
		return q.list();
	}

	@Override
	public List<DesignForX> getDfxByProdId(int productId) {
		List<DesignForX> dfxByProdId = new ArrayList<DesignForX>();
		
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select drm from DfXRelMatrix drm where drm.product.id=:productId");
		q.setInteger("productId", productId);
		List<DfXRelMatrix> relMatByProdId = q.list();
		
		for(int i=0; i<relMatByProdId.size(); i++){
			boolean flag = true;
			DfXRelMatrix dfxRM = relMatByProdId.get(i);
			DfXGuidelines dfxG = dfxRM.getDfxGuideline();
			DesignForX dfx = dfxG.getDfx();
			for(int j=0; j<dfxByProdId.size(); j++){
				if(dfx == dfxByProdId.get(j)){
					flag = false;
					continue;
				}
			}
			if (flag){
				dfxByProdId.add(dfx);
			}
		}
		return dfxByProdId;
	}

	@Override
	public List<DfXGuidelines> getDfXGuidelinesByProdId(int productId) {
		List<DfXGuidelines> dfxGuidelinesByProdId = new ArrayList<DfXGuidelines>();
		
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select drm from DfXRelMatrix drm where drm.product.id=:productId");
		q.setInteger("productId", productId);
		List<DfXRelMatrix> relMatByProdId = q.list();
		
		for(int i=0; i<relMatByProdId.size(); i++){
			DfXRelMatrix dfxRM = relMatByProdId.get(i);
			dfxGuidelinesByProdId.add(dfxRM.getDfxGuideline());
		}
		return dfxGuidelinesByProdId;
	}

	@Override
	public List<DfXImportances> getDfXImportancesByProdId(int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select i from DfXImportances i where i.product.id=:productId");
		q.setInteger("productId", productId);
		return q.list();
	}

	@Override
	public List<Resource> getResourceByActNameandProdId(String activityName,
			int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select r from Resource r where r.activityName=:activityName and r.productId=:productId");
		q.setString("activityName", activityName);
		q.setInteger("productId", productId);
		return q.list();
	}

	@Override
	public List<Requirements> getReqsByAuthorandProdId(String author,
			int productId) {
		Session session = HibernateUtil.currentSession();
		Query q = session.createQuery("select r from Requirements r where r.author=:author and r.productId=:productId");
		q.setString("author", author);
		q.setInteger("productId", productId);
		
		return q.list();
	}
	
}
